DDL Commands

There are several types of Hive DDL commands, we commonly use. such as:

• Create Database Statement
• Drop database
• Hive Show Database
• Creating Hive Tables
• Browse the table
• Hive Load Data
• Altering and Dropping Tables
• Hive Select Data from Table

Let’s discuss each Hive DDL commands in detail:

Create Database

create database<data base name> to create the new database in the Hive.

CREATE DATABASE [IF NOT EXISTS] db_name;

CREATE DATABASE IF NOT EXISTS DWDEV;
or

CREATE SCHEMA hivedb; 

The optional IF NOT EXISTS clause causes the error to be suppressed if the table already exists.

The database creates in a default location of the Hive warehouse. Hive database store in a HDFS directorey /user/hive/warehouse by default. The following command creates a table with in location of /user/hive/warehouse/dwdev.db. Moreover, by specifying the desired directory in hive.metastore.warehouse.dir configuration parameter present in the hive-site.xml, one can change it.Default database name in Hive is Default.

Drop Database

drop databases if exists dwde;
drop database dwdev;

Show Database

To see all available database in Hive
show databases;

To find/display current database information in Hive Command Line Interface.

set hive.cli.print.current.db=true;

Use Database

To set the particular Database
USE <DATABASE NAME>

Eg. use dwdev;

Create Hive Table
  create table  emp

 (
    empno       int COMMENT 'Employee Id number',
    ename       varchar(20),
    job             varchar(20),
    sal             float,
    comm        float COMMENT 'Icentive',
    deptno      int 
)  ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
   LINES TERMINATED BY '\n' STORED AS TEXTFILE;

Describe Table in Hive

To see table primary info of Hive table

• describe table_name;
• describe emp;

To see more detailed information about the table

• describe extended table_name
• describe extended  emp;

To see code all information in formatted

• describe formatted table_name;
• describe formatted emp;

Show Table DDL In Hive
show crate table table_Name

Show Table DDL In Hive
show crate table table_Name

Add and Remove Column

alter table emp rename to employee; 

alter table emp add columns (address String); 
alter table hive_table emp columns (phone_number int comment 'mobile number'); 
alter table emp replace columns (sal int, weight string, baz int comment 'baz replaces new_col1');